package cn.renxuan.repository;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.security.Key;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.StringJoiner;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionException;
import java.util.concurrent.Executor;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.function.BiFunction;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Collectors;

import cn.renxuan.CommonUtil;
import cn.renxuan.entity.Entity;
import cn.renxuan.querys.BaseQuery;
import cn.renxuan.reflection.ClassHelper;
import cn.renxuan.repository.DynamicParameters;
import cn.renxuan.util.TriFunction;

public class BaseRepository<TEntity extends Entity<TPrimaryKey>, TPrimaryKey>
        implements IBaseRepository<TEntity, TPrimaryKey> {

    static {
        try {
            DBHelper.RegisterDbInfo();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private Class<TEntity> entityClass;

    public BaseRepository(Class<TEntity> classz) {
        this.entityClass = classz;
    }

    public BaseRepository() {

    }

    private Connection getReadConnection() throws Exception {
        return DBHelper.getConnection();
    }

    private Connection getWriteConnection() throws Exception {
        return DBHelper.getConnection();
    }

    private String getTableName(Class<?> entityClass) {
        DbTable dbTable = entityClass.getAnnotation(DbTable.class);
        if (dbTable != null) {
            return dbTable.name();
        }
        return entityClass.getSimpleName().toLowerCase(); // 默认表名
    }

    private <T> List<T> convertResultSet(ResultSet rs, Class<T> clazz) throws Exception {
        List<T> result = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        // 获取clazz的泛型类型所有字段包括父类的
        List<Field> fields = ClassHelper.getAllFields(clazz);
        int columnCount = metaData.getColumnCount();
        while (rs.next()) {
            T obj = clazz.getDeclaredConstructor().newInstance();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i); // 或 getColumnName
                Object value = rs.getObject(i);

                Field field = fields.stream().filter(f -> columnName.equalsIgnoreCase(f.getName())).findFirst()
                        .orElse(null);
                if (field == null) {
                    // 如果没有找到匹配的字段，尝试使用注解Column查找
                    field = fields.stream()
                            .filter(f -> f.isAnnotationPresent(Column.class) &&
                                    f.getAnnotation(Column.class).name().equalsIgnoreCase(columnName))
                            .findFirst().orElse(null);
                }

                if (field != null) {
                    field.setAccessible(true);
                    field.set(obj, convertType(value, field.getType()));
                }

                // // 查找匹配字段（支持注解）
                // for (Field field : clazz.getDeclaredFields()) {
                // Column column = field.getAnnotation(Column.class);
                // String fieldName = (column != null) ? column.name() : field.getName();

                // if (fieldName.equalsIgnoreCase(columnName)) {
                // field.setAccessible(true);
                // field.set(obj, convertType(value, field.getType()));
                // break;
                // }
                // }
            }
            result.add(obj);
        }
        return result;
    }

    // 类型转换辅助方法
    private Object convertType(Object value, Class<?> targetType) {
        if (value == null)
            return null;
        if (targetType == LocalDateTime.class && value instanceof Timestamp) {
            return ((Timestamp) value).toLocalDateTime();
        }
        // 其他类型转换...
        return value;
    }

    /*
     * 释放数据库对象
     */
    public void disposeDbResource(Connection conn, Statement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException ex) {
            CommonUtil.printThreadLog("关闭数据库PreparedStatement异常：" + ex.getMessage());
        }

        try {
            if (conn != null) {
                conn.close();
            }

        } catch (SQLException ex) {
            CommonUtil.printThreadLog("关闭数据库连接异常：" + ex.getMessage());
        }
    }

    public void disposeDbResource(Connection conn) {
        disposeDbResource(conn, null);
    }

    public void disposeDbResource(Statement ps) {
        disposeDbResource(null, ps);
    }

    /**
     * 事务处理
     * 
     * @param action 要在事务中执行的操作
     * @return CompletableFuture 包含事务执行结果
     * @param <TResult> 返回结果类型
     */
    public <TResult> CompletableFuture<TResult> transactionProcess(
            Function<Connection, CompletableFuture<TResult>> action) {
        BiFunction<Connection, TResult, CompletableFuture<TResult>> biFunction = (conn, r) -> {
            return action.apply(conn);
        };
        return this.<TResult, TResult>transactionProcess(biFunction, null, Connection.TRANSACTION_REPEATABLE_READ);
    }

    /**
     * 事务处理
     * 
     * @param action 要在事务中执行的操作
     * @param level  隔离级别 (默认为 REPEATABLE_READ)
     * @return CompletableFuture 包含事务执行结果
     * @param <TResult> 返回结果类型
     */
    public <TResult> CompletableFuture<TResult> transactionProcess(
            Function<Connection, CompletableFuture<TResult>> action,
            int level) {
        BiFunction<Connection, TResult, CompletableFuture<TResult>> biFunction = (conn, r) -> {
            return action.apply(conn);
        };
        return this.<TResult, TResult>transactionProcess(biFunction, null, level);
    }

    /**
     * 事务处理
     * 
     * @param action                  要在事务中执行的操作
     * @param transactioningPreAction 要在事务开启后预处理执行的操作
     * @param level                   隔离级别 (默认为 REPEATABLE_READ)
     * @return CompletableFuture 包含事务执行结果
     * @param <TResult> 返回结果类型
     */
    public <TPreResult, TResult> CompletableFuture<TResult> transactionProcess(
            BiFunction<Connection, TPreResult, CompletableFuture<TResult>> action,
            Function<Connection, CompletableFuture<TPreResult>> transactioningPreAction,
            int level) {
        return this.<TPreResult, TResult, TResult>transactionProcess(action, transactioningPreAction, null, level);
    }

    /**
     * 事务处理
     * 
     * @param action                    要在事务中执行的操作
     * @param transactioningPreAction   要在事务开启后预处理执行的操作
     * @param transactioningAfterAction 要在事务处理主处理程序之后执行的操作
     * @param level                     隔离级别 (默认为 REPEATABLE_READ)
     * @return CompletableFuture 包含事务执行结果
     * @param <TResult> 返回结果类型
     */
    public <TPreResult, TResult, TAfterResult> CompletableFuture<TAfterResult> transactionProcess(
            BiFunction<Connection, TPreResult, CompletableFuture<TResult>> action,
            Function<Connection, CompletableFuture<TPreResult>> transactioningPreAction,
            TriFunction<Connection, TPreResult, TResult, CompletableFuture<TAfterResult>> transactioningAfterAction,
            int level) {
        return CompletableFuture.supplyAsync(() -> {
            Connection conn = null;
            try {
                conn = this.getWriteConnection();

                if (conn == null || conn.isClosed()) {
                    throw new SQLException("Connection is not available");
                }

                // 设置隔离级别并开始事务
                int originalIsolation = conn.getTransactionIsolation();
                conn.setTransactionIsolation(level);
                conn.setAutoCommit(false);

                TPreResult preResult = null;
                if (transactioningPreAction != null) {
                    preResult = transactioningPreAction.apply(conn).get();
                }

                TResult result = null;
                if (action != null) {
                    // 在Java中，通常使用同一个Connection对象管理事务
                    result = action.apply(conn, preResult).get(); // 阻塞获取结果
                }

                TAfterResult afterResult = null;
                if (transactioningAfterAction != null) {
                    transactioningAfterAction.apply(conn, preResult, result).get();
                }

                // 提交事务
                conn.commit();
                return afterResult;

            } catch (Exception ex) {
                if (conn != null) {
                    try {
                        conn.rollback();
                    } catch (Exception e) {
                        throw new CompletionException("transaction rollback failed", ex);
                    }
                }
                throw new CompletionException("Failed to db operation", ex);
            } finally {
                disposeDbResource(conn);
            }

        });
    }

    /*
     * 获取指定查询条件的实体列表分页基础方法
     */
    private <TQueryEntity, TResult> CompletableFuture<Map.Entry<Integer, List<TResult>>> getBaseNewPageList(
            BaseQuery<TQueryEntity> querys,
            Class<TResult> clazz,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TQueryEntity>> appendWhereAction,
            String tableAlias) throws SQLException {

        return CompletableFuture.supplyAsync(() -> {
            Map.Entry<Integer, List<TResult>> retVal = null;
            String cpTableName = tableName;
            String cpFieldNames = fieldNames;
            String cpTableAlias = tableAlias;
            int count = 0;
            List<TResult> searchResult = null;
            try {
                Map<String, DynamicParameters> querySqls = querys.getQuerySql();

                if (querySqls == null || querySqls.isEmpty()) {
                    throw new RuntimeException("未提供查询语句");
                }

                Map.Entry<String, DynamicParameters> firstQuery = querySqls.entrySet().iterator().next();
                String strWhere = firstQuery.getKey();
                DynamicParameters parameters = firstQuery.getValue();

                if (querys.isAppendFormatWhere()) {
                    if (appendWhereAction != null) {
                        AppendWhereActionParam<TQueryEntity> param = new AppendWhereActionParam<>();
                        param.setParameters(parameters);
                        param.setQuerys(querys);
                        appendWhereAction.accept(param);
                        strWhere = String.format(strWhere, " " + param.getAppendWhereSql() + " ");
                    } else {
                        strWhere = String.format(strWhere, " ");
                    }
                }

                if (cpTableAlias == null || cpTableAlias.trim().isEmpty()) {
                    Class<?> queryType = querys.getClass();
                    try {
                        // 获取当前类的所有字段 包含父类字段
                        List<Field> fields = ClassHelper.getAllFields(queryType);

                        Field tableAliasKeyProp = fields.stream()
                                .filter(f -> "defaultTableAliasKey".equals(f.getName())).findFirst().get();

                        // queryType.getDeclaredField("defaultTableAliasKey");

                        if (tableAliasKeyProp != null) {
                            tableAliasKeyProp.setAccessible(true);
                            String tableAliasKeyPropVal = (String) tableAliasKeyProp.get(querys);

                            if (tableAliasKeyPropVal != null && !tableAliasKeyPropVal.isEmpty()) {
                                try {
                                    Field tableAliasField = fields.stream()
                                            .filter(f -> tableAliasKeyPropVal.equals(f.getName())).findFirst().get();
                                    // queryType.getDeclaredField();
                                    tableAliasField.setAccessible(true);
                                    String tableAliasFieldVal = (String) tableAliasField.get(querys);
                                    if (tableAliasFieldVal != null && !tableAliasFieldVal.isEmpty()) {
                                        cpTableAlias = tableAliasFieldVal;
                                    }
                                } catch (Exception e) {
                                    // Try to find as property
                                    try {
                                        java.lang.reflect.Method tableAliasProp = queryType.getDeclaredMethod("get" +
                                                tableAliasKeyPropVal.substring(0, 1).toUpperCase() +
                                                tableAliasKeyPropVal.substring(1));
                                        tableAliasProp.setAccessible(true);
                                        String tableAliasPropVal = (String) tableAliasProp.invoke(querys);
                                        if (tableAliasPropVal != null) {
                                            cpTableAlias = tableAliasPropVal;
                                        }
                                    } catch (NoSuchMethodException | IllegalAccessException
                                            | InvocationTargetException ex) {
                                        System.out.println(ex.getMessage());
                                    }
                                }
                            }
                        }
                    } catch (Exception e) {
                        System.out.println(e.getMessage());
                    }
                }

                if (cpTableName == null || cpTableName.trim().isBlank()) {
                    cpTableName = getTableName(clazz);
                    if (cpTableName == null || cpTableName.trim().isBlank()) {
                        cpTableName = clazz.getName();
                    }
                }

                cpTableName = " " + cpTableName + " " + (cpTableAlias != null ? cpTableAlias : "") + " ";

                StringBuilder sbsql = new StringBuilder();
                StringBuilder sbCount = new StringBuilder();
                if (cpFieldNames == null || cpFieldNames.trim().isEmpty()) {
                    cpFieldNames = " * ";
                }
                sbsql.append(String.format("SELECT %s FROM %s WHERE 1=1 %s", cpFieldNames, cpTableName, strWhere));
                sbCount.append(String.format("SELECT COUNT(1) FROM %s WHERE 1=1 %s", cpTableName, strWhere));
                // Get order by
                if (querySqls.size() > 1) {
                    Map.Entry<String, DynamicParameters> orderSql = querySqls.entrySet().stream()
                            .skip(1)
                            .findFirst()
                            .orElse(null);

                    if (orderSql != null && orderSql.getKey() != null && !orderSql.getKey().isEmpty()) {
                        sbsql.append(orderSql.getKey());
                        // Assuming DynamicParameters has a method to add all parameters
                        parameters.addAll(orderSql.getValue());
                    }
                }

                Integer skip = querys.getSkip();
                Integer pageCount = querys.getCount();
                boolean isGetSearchList = querys.getSearchList() != null ? querys.getSearchList() : false;
                if (skip != null && skip > -1 && pageCount != null && pageCount > 0 && isGetSearchList) {
                    sbsql.append(String.format(" LIMIT %d,%d ", querys.getSkip(), querys.getCount()));
                }

                sbsql.append(" ; ");
                sbCount.append(" ; ");

                if (isGetSearchList) {
                    try (PreparedStatement stmt = conn.prepareStatement(sbsql.toString())) {
                        // Set parameters from DynamicParameters to PreparedStatement
                        // setParameters(stmt, parameters);
                        parameters.applyTo(stmt);

                        try (ResultSet rs = stmt.executeQuery()) {
                            searchResult = convertResultSet(rs, clazz);
                        } catch (Exception e) {
                            throw new CompletionException(e);
                        }
                    }
                }

                boolean isGetCount = querys.getSearchCount() != null ? querys.getSearchCount() : false;
                if (isGetCount) {
                    try (PreparedStatement stmt = conn.prepareStatement(sbCount.toString())) {
                        // Set parameters from DynamicParameters to PreparedStatement
                        // setParameters(stmt, parameters);
                        parameters.applyTo(stmt);

                        try (ResultSet rs = stmt.executeQuery()) {
                            if (rs.next()) {
                                // 获取首行首列数据（索引从 1 开始）
                                count = rs.getInt(1); // 或 rs.getInt("COUNT(*)")
                            }
                        } catch (Exception e) {
                            throw new CompletionException(e);
                        }
                    }
                }
            } catch (SQLException e) {
                throw new CompletionException(e);
            } finally {

            }
            retVal = new AbstractMap.SimpleEntry<>(count, searchResult);
            return retVal;
        });
    }

    public <TQueryEntity, TResult> CompletableFuture<Map.Entry<Integer, List<TResult>>> getNewEntityAndResultPageList(
            BaseQuery<TQueryEntity> querys,
            Class<TResult> clazz,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TQueryEntity>> appendWhereAction,
            String tableAlias) throws Exception {
        return this.<TQueryEntity, TResult>getBaseNewPageList(querys, clazz, tableName, fieldNames, conn,
                appendWhereAction, tableAlias);
    }

    public <TResult> CompletableFuture<Map.Entry<Integer, List<TResult>>> getNewResultPageList(
            BaseQuery<TEntity> querys,
            Class<TResult> clazz,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TEntity>> appendWhereAction,
            String tableAlias) throws Exception {
        return this.<TEntity, TResult>getBaseNewPageList(querys, clazz, tableName, fieldNames, conn,
                appendWhereAction, tableAlias);
    }

    public <TResult> CompletableFuture<Map.Entry<Integer, List<TResult>>> getNewResultPageList(
            BaseQuery<TEntity> querys,
            Class<TResult> clazz,
            String tableName,
            String fieldNames,
            Consumer<AppendWhereActionParam<TEntity>> appendWhereAction,
            String tableAlias) throws Exception {
        final Connection conn = getReadConnection();
        return this.<TEntity, TResult>getBaseNewPageList(querys, clazz, tableName, fieldNames, conn,
                appendWhereAction, tableAlias).handle((result, ex) -> {
                    disposeDbResource(conn);
                    if (ex != null) {
                        throw new CompletionException(ex);
                    }
                    return result;
                });
    }

    public CompletableFuture<Map.Entry<Integer, List<TEntity>>> getNewPageList(BaseQuery<TEntity> querys)
            throws Exception {
        CompletableFuture<Map.Entry<Integer, List<TEntity>>> future = null;
        if (entityClass != null) {
            final Connection conn = getReadConnection();
            future = this.getBaseNewPageList(querys, entityClass, null, null, conn,
                    null, null).handle((result, ex) -> {
                        disposeDbResource(conn);
                        if (ex != null) {
                            throw new CompletionException(ex);
                        }
                        return result;
                    });
        }
        return future;
    }

    public CompletableFuture<Map.Entry<Integer, List<TEntity>>> getNewPageList(BaseQuery<TEntity> querys,
            String tableName,
            String fieldNames,
            Consumer<AppendWhereActionParam<TEntity>> appendWhereAction,
            String tableAlias) throws Exception {
        CompletableFuture<Map.Entry<Integer, List<TEntity>>> future = null;
        if (entityClass != null) {
            final Connection conn = getReadConnection();
            future = this.getBaseNewPageList(querys, entityClass, tableName, fieldNames, conn,
                    appendWhereAction, tableAlias).handle((result, ex) -> {
                        disposeDbResource(conn);
                        if (ex != null) {
                            throw new CompletionException(ex);
                        }
                        return result;
                    });
        }
        return future;
    }

    public CompletableFuture<Map.Entry<Integer, List<TEntity>>> getNewPageList(BaseQuery<TEntity> querys,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TEntity>> appendWhereAction,
            String tableAlias) throws Exception {
        CompletableFuture<Map.Entry<Integer, List<TEntity>>> future = null;
        if (entityClass != null) {
            future = this.getBaseNewPageList(querys, entityClass, tableName, fieldNames, conn,
                    appendWhereAction, tableAlias);
        }
        return future;
    }

    /*
     * 获取指定查询条件的实体列表基础方法
     */
    private <TQueryEntity, TResult> CompletableFuture<List<TResult>> getBaseNewListForLock(
            BaseQuery<TQueryEntity> querys,
            Class<TResult> clazz,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TQueryEntity>> appendWhereAction,
            String tableAlias,
            EMySqlLockType lockType) throws SQLException {

        return CompletableFuture.supplyAsync(() -> {
            String cpTableName = tableName;
            String cpFieldNames = fieldNames;
            String cpTableAlias = tableAlias;
            try {
                Map<String, DynamicParameters> querySqls = querys.getQuerySql();

                if (querySqls == null || querySqls.isEmpty()) {
                    throw new RuntimeException("未提供查询语句");
                }

                Map.Entry<String, DynamicParameters> firstQuery = querySqls.entrySet().iterator().next();
                String strWhere = firstQuery.getKey();
                DynamicParameters parameters = firstQuery.getValue();

                if (querys.isAppendFormatWhere()) {
                    if (appendWhereAction != null) {
                        AppendWhereActionParam<TQueryEntity> param = new AppendWhereActionParam<>();
                        param.setParameters(parameters);
                        param.setQuerys(querys);
                        appendWhereAction.accept(param);
                        strWhere = String.format(strWhere, " " + param.getAppendWhereSql() + " ");
                    } else {
                        strWhere = String.format(strWhere, " ");
                    }
                }

                if (cpTableAlias == null || cpTableAlias.trim().isEmpty()) {
                    Class<?> queryType = querys.getClass();
                    try {
                        Field tableAliasKeyProp = queryType.getDeclaredField("defaultTableAliasKey");
                        tableAliasKeyProp.setAccessible(true);
                        String tableAliasKeyPropVal = (String) tableAliasKeyProp.get(querys);

                        if (tableAliasKeyPropVal != null && !tableAliasKeyPropVal.isEmpty()) {
                            try {
                                Field tableAliasField = queryType.getDeclaredField(tableAliasKeyPropVal);
                                tableAliasField.setAccessible(true);
                                String tableAliasFieldVal = (String) tableAliasField.get(querys);
                                if (tableAliasFieldVal != null && !tableAliasFieldVal.isEmpty()) {
                                    cpTableAlias = tableAliasFieldVal;
                                }
                            } catch (NoSuchFieldException e) {
                                // Try to find as property
                                try {
                                    java.lang.reflect.Method tableAliasProp = queryType.getDeclaredMethod("get" +
                                            tableAliasKeyPropVal.substring(0, 1).toUpperCase() +
                                            tableAliasKeyPropVal.substring(1));
                                    tableAliasProp.setAccessible(true);
                                    String tableAliasPropVal = (String) tableAliasProp.invoke(querys);
                                    if (tableAliasPropVal != null) {
                                        cpTableAlias = tableAliasPropVal;
                                    }
                                } catch (NoSuchMethodException | IllegalAccessException
                                        | InvocationTargetException ex) {
                                    // Ignore if not found
                                }
                            }
                        }
                    } catch (NoSuchFieldException | IllegalAccessException e) {
                        // Ignore if not found
                    }
                }

                if (cpTableName == null || cpTableName.trim().isBlank()) {
                    cpTableName = getTableName(clazz);
                    if (cpTableName == null || cpTableName.trim().isBlank()) {
                        cpTableName = clazz.getName();
                    }
                }

                cpTableName = " " + cpTableName + " " + (cpTableAlias != null ? cpTableAlias : "") + " ";

                StringBuilder sbsql = new StringBuilder();
                if (cpFieldNames == null || cpFieldNames.trim().isEmpty()) {
                    cpFieldNames = " * ";
                }
                sbsql.append(String.format("SELECT %s FROM %s WHERE 1=1 %s", cpFieldNames, cpTableName, strWhere));

                // Get order by
                if (querySqls.size() > 1) {
                    Map.Entry<String, DynamicParameters> orderSql = querySqls.entrySet().stream()
                            .skip(1)
                            .findFirst()
                            .orElse(null);

                    if (orderSql != null && orderSql.getKey() != null && !orderSql.getKey().isEmpty()) {
                        sbsql.append(orderSql.getKey());
                        // Assuming DynamicParameters has a method to add all parameters
                        parameters.addAll(orderSql.getValue());
                    }
                }

                Integer skip = querys.getSkip();
                Integer pageCount = querys.getCount();
                boolean isGetSearchList = querys.getSearchList() != null ? querys.getSearchList() : false;
                if (skip != null && skip > -1 && pageCount != null && pageCount > 0 && isGetSearchList) {
                    sbsql.append(String.format(" LIMIT %d,%d ", skip, pageCount));
                }

                // if (querys.getSkip() > -1 && querys.getCount() > 0 &&
                // querys.getSearchList() != null && querys.getSearchList()) {
                // sbsql.append(String.format(" LIMIT %d,%d ", querys.getSkip(),
                // querys.getCount()));
                // }

                if (lockType == EMySqlLockType.X) {
                    sbsql.append(" FOR UPDATE ; ");
                } else if (lockType == EMySqlLockType.S) {
                    sbsql.append(" LOCK IN SHARE MODE ; ");
                } else {
                    sbsql.append(" ; ");
                }

                List<TResult> searchResult = null;
                // if (conn == null) {
                // conn = getReadConnection(); // You need to implement this
                // }

                if (isGetSearchList) {
                    try (PreparedStatement stmt = conn.prepareStatement(sbsql.toString())) {
                        // Set parameters from DynamicParameters to PreparedStatement
                        // setParameters(stmt, parameters);
                        parameters.applyTo(stmt);

                        try (ResultSet rs = stmt.executeQuery()) {
                            searchResult = convertResultSet(rs, clazz);
                            // searchResult = new ArrayList<>();
                            // while (rs.next()) {
                            // // Map ResultSet to TResult
                            // TResult result = mapResultSetToTResult(rs);
                            // searchResult.add(result);
                            // }
                        } catch (Exception e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }

                return searchResult;
            } catch (SQLException e) {
                // try {
                // conn.rollback();
                // } catch (SQLException e1) {
                // // TODO Auto-generated catch block
                // e1.printStackTrace();
                // }
                // throw new RuntimeException(e);
                throw new CompletionException(e);
            } finally {
                // if (conn != null) {
                // try {
                // conn.close();
                // } catch (SQLException e) {
                // // Log error
                // }
                // }
            }
        });
    }

    public CompletableFuture<List<TEntity>> getNewListForLock(BaseQuery<TEntity> querys,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TEntity>> appendWhereAction,
            String tableAlias,
            EMySqlLockType lockType) throws Exception {
        CompletableFuture<List<TEntity>> future = null;
        if (entityClass != null) {
            future = this.getBaseNewListForLock(querys, entityClass, tableName, fieldNames, conn,
                    appendWhereAction, tableAlias, lockType);
        }
        return future;
    }

    /*
     * 获取指定查询条件的实体列表
     */
    public <TResult> CompletableFuture<List<TResult>> getNewListForLock(BaseQuery<TEntity> querys,
            Class<TResult> clazz,
            String tableName,
            String fieldNames,
            Connection conn,
            Consumer<AppendWhereActionParam<TEntity>> appendWhereAction,
            String tableAlias,
            EMySqlLockType lockType) throws Exception {

        CompletableFuture<List<TResult>> future = this.getBaseNewListForLock(
                querys,
                clazz,
                tableName,
                fieldNames,
                conn,
                appendWhereAction,
                tableAlias,
                lockType);
        return future;

    }

    /*
     * 新增记录
     */
    public CompletableFuture<Integer> addAsync(Class<TEntity> entityClaszz, TEntity entity,
            List<String> ignoreColumns)
            throws Exception {

        final Connection conn = getWriteConnection();
        conn.setAutoCommit(false);
        return addAsync(entityClaszz, entity, conn, ignoreColumns)
                .handle((a, ex) -> {
                    if (ex != null) {
                        CommonUtil.printThreadLog("出现异常：" + ex.getMessage());
                        try {
                            conn.rollback();
                        } catch (SQLException e) {
                            CommonUtil.printThreadLog("回滚事务异常：" + e.getMessage());
                        }
                    } else {
                        if (a > 0) {
                            try {
                                conn.commit();
                            } catch (SQLException e) {
                                CommonUtil.printThreadLog("提交事务异常：" + e.getMessage());
                                a = 0;
                            }
                        }
                    }
                    disposeDbResource(conn);
                    return a;
                });

    }

    /**
     * 新增记录
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entity        添加的对象
     * @param conn          数据库连接对象
     * @param ignoreColumns 忽略添加的字段
     * @return
     */
    public CompletableFuture<Integer> addAsync(Class<TEntity> entityClaszz, TEntity entity,
            Connection conn, List<String> ignoreColumns) {

        return CompletableFuture.supplyAsync(() -> {
            int affectRows = 0;
            PreparedStatement ps = null;
            try {
                String tempTableName = getTableName(entityClaszz);
                // 获取所有声明的字段
                // List<String> addFieldNames = new ArrayList<String>();
                Map<String, Object> dicAddFieldNames = new HashMap<String, Object>();
                Field[] fields = entityClaszz.getDeclaredFields();
                for (Field field : fields) {
                    // 过滤掉不需要新增的字段
                    if (ignoreColumns != null && ignoreColumns.contains(field.getName())) {
                        continue;
                    }
                    // addFieldNames.add(field.getName());
                    field.setAccessible(true); // 设置可访问性
                    // 获取字段值
                    dicAddFieldNames.put(field.getName(), field.get(entity));
                }
                if (dicAddFieldNames.size() > 0) {
                    Set<String> keys = dicAddFieldNames.keySet();

                    // 方法1：使用传统的 for循环 加 StringJoiner
                    StringJoiner joiner = new StringJoiner(",");
                    for (int i = 0; i < keys.size(); i++) {
                        joiner.add("?");
                    }
                    String strVal = joiner.toString();

                    // 方法2：使用 Java 8 Stream API
                    // String strVal = keys.stream().map(key ->
                    // "?").collect(Collectors.joining(","));

                    // 预编译sql字符串
                    String insertSql = String.format(" insert into %s(%s) values(%s); ", tempTableName,
                            String.join(",", keys), strVal);
                    ps = conn.prepareStatement(insertSql);
                    // 填充占位符
                    int i = 1;
                    for (String key : keys) {
                        ps.setObject(i, dicAddFieldNames.get(key));
                        i++;
                    }

                    // ps 执行操作
                    affectRows = ps.executeUpdate();
                }
            } catch (Exception e) {
                // 包装后抛出;
                throw new CompletionException(e);
            } finally {
                if (ps != null) {
                    disposeDbResource(ps);
                }
            }
            return affectRows;
        });
    }

    /**
     * 新增记录并且返回自增id
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entity        添加的对象
     * @param ignoreColumns 忽略添加的字段
     * @return 返回自增id
     * @throws Exception
     */
    public CompletableFuture<Number> addAndRetIdAsync(Class<TEntity> entityClaszz, TEntity entity,
            List<String> ignoreColumns) throws Exception {
        final Connection conn = getWriteConnection();
        conn.setAutoCommit(false);
        return addAndRetIdAsync(entityClaszz, entity, conn, ignoreColumns)
                .handle((a, ex) -> {
                    if (ex != null) {
                        CommonUtil.printThreadLog("出现异常：" + ex.getMessage());
                        try {
                            conn.rollback();
                        } catch (SQLException e) {
                            CommonUtil.printThreadLog("回滚事务异常：" + e.getMessage());
                        }
                    } else {
                        if (a.longValue() > 0) {
                            try {
                                conn.commit();
                            } catch (SQLException e) {
                                CommonUtil.printThreadLog("提交事务异常：" + e.getMessage());
                                a = 0;
                            }
                        }
                    }
                    disposeDbResource(conn);
                    return a;
                });
    }

    /**
     * 新增记录并且返回自增id
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entity        添加的对象
     * @param conn          数据库连接对象
     * @param ignoreColumns 忽略添加的字段
     * @return 返回自增id
     */
    public CompletableFuture<Number> addAndRetIdAsync(Class<TEntity> entityClaszz, TEntity entity,
            Connection conn, List<String> ignoreColumns) {
        return CompletableFuture.supplyAsync(() -> {
            Number id = 0;
            PreparedStatement ps = null;
            try {
                String tempTableName = getTableName(entityClaszz);
                // 获取所有声明的字段
                // List<String> addFieldNames = new ArrayList<String>();
                Map<String, Object> dicAddFieldNames = new HashMap<String, Object>();
                Field[] fields = entityClaszz.getDeclaredFields();
                for (Field field : fields) {
                    // 过滤掉不需要新增的字段
                    if (ignoreColumns != null && ignoreColumns.contains(field.getName())) {
                        continue;
                    }
                    // addFieldNames.add(field.getName());
                    field.setAccessible(true); // 设置可访问性
                    // 获取字段值
                    dicAddFieldNames.put(field.getName(), field.get(entity));
                }
                if (dicAddFieldNames.size() > 0) {
                    Set<String> keys = dicAddFieldNames.keySet();

                    // 方法1：使用传统的 for循环 加 StringJoiner
                    StringJoiner joiner = new StringJoiner(",");
                    for (int i = 0; i < keys.size(); i++) {
                        joiner.add("?");
                    }
                    String strVal = joiner.toString();

                    // 方法2：使用 Java 8 Stream API
                    // String strVal = keys.stream().map(key ->
                    // "?").collect(Collectors.joining(","));

                    // 预编译sql字符串
                    String insertSql = String.format(" insert into %s(%s) values(%s); ", tempTableName,
                            String.join(",", keys), strVal);
                    ps = conn.prepareStatement(insertSql);
                    // 填充占位符
                    int i = 1;
                    for (String key : keys) {
                        ps.setObject(i, dicAddFieldNames.get(key));
                        i++;
                    }

                    // ps 执行操作
                    ps.executeUpdate();

                    try (ResultSet rs = ps.getGeneratedKeys()) {
                        ResultSetMetaData metaData = rs.getMetaData();
                        String columnClassName = metaData.getColumnClassName(1);
                        if (Long.class.getName().equalsIgnoreCase(columnClassName)) {
                            id = rs.getLong(1);
                        } else if (Short.class.getName().equalsIgnoreCase(columnClassName)) {
                            id = rs.getShort(1);
                        } else {
                            id = rs.getInt(1);
                        }
                    }
                }
            } catch (Exception e) {
                // 包装后抛出;
                throw new CompletionException(e);
            } finally {
                if (ps != null) {
                    disposeDbResource(ps);
                }
            }
            return id;
        });
    }

    /**
     * 批量新增并返回受影响的行数
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entities      添加的对象集合
     * @param ignoreColumns 忽略添加的字段
     * @return 受影响的行数
     * @throws Exception
     */
    public CompletableFuture<Integer> batchAddAsync(Class<TEntity> entityClaszz, List<TEntity> entities,
            List<String> ignoreColumns) throws Exception {
        return this.batchAddAsync(entityClaszz, entities, ignoreColumns, 1000);
    }

    /**
     * 批量新增并返回受影响的行数
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entities      添加的对象集合
     * @param ignoreColumns 忽略添加的字段
     * @param step          插入数据的步长
     * @return 受影响的行数
     * @throws Exception
     */
    public CompletableFuture<Integer> batchAddAsync(Class<TEntity> entityClaszz, List<TEntity> entities,
            List<String> ignoreColumns, int step) throws Exception {
        final Connection conn = getWriteConnection();
        conn.setAutoCommit(false);
        return this.batchAddAsync(entityClaszz, entities, ignoreColumns, conn, step).handle((a, ex) -> {
            if (ex != null) {
                CommonUtil.printThreadLog("出现异常：" + ex.getMessage());
                try {
                    conn.rollback();
                } catch (SQLException e) {
                    CommonUtil.printThreadLog("回滚事务异常：" + e.getMessage());
                }
            } else {
                if (a > 0) {
                    try {
                        conn.commit();
                    } catch (SQLException e) {
                        CommonUtil.printThreadLog("提交事务异常：" + e.getMessage());
                        a = 0;
                    }
                }
            }
            disposeDbResource(conn);
            return a;
        });
    }

    /**
     * 批量新增并返回受影响的行数
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entities      添加的对象集合
     * @param conn          数据库连接对象
     * @param ignoreColumns 忽略添加的字段
     * @param step          插入数据的步长
     * @return 受影响的行数
     */
    public CompletableFuture<Integer> batchAddAsync(Class<TEntity> entityClaszz, List<TEntity> entities,
            List<String> ignoreColumns,
            Connection conn) throws Exception {
        return this.batchAddAsync(entityClaszz, entities, ignoreColumns, conn, 1000).handle((a, ex) -> {
            if (ex != null) {
                CommonUtil.printThreadLog("出现异常：" + ex.getMessage());
                try {
                    conn.rollback();
                } catch (SQLException e) {
                    CommonUtil.printThreadLog("回滚事务异常：" + e.getMessage());
                }
            } else {
                if (a > 0) {
                    try {
                        conn.commit();
                    } catch (SQLException e) {
                        CommonUtil.printThreadLog("提交事务异常：" + e.getMessage());
                        a = 0;
                    }
                }
            }
            disposeDbResource(conn);
            return a;
        });
    }

    /**
     * 批量新增并返回受影响的行数
     * 
     * @param entityClaszz  添加对象的class对象
     * @param entities      添加的对象集合
     * @param ignoreColumns 忽略添加的字段
     * @param conn          数据库连接对象
     * @param step          插入数据的步长
     * @return 受影响的行数
     */
    public CompletableFuture<Integer> batchAddAsync(Class<TEntity> entityClaszz, List<TEntity> entities,
            List<String> ignoreColumns, Connection conn, int step) {
        return CompletableFuture.supplyAsync(() -> {
            int affectedRow = 0;
            PreparedStatement ps = null;
            try {
                String tempTableName = getTableName(entityClaszz);
                // 获取所有声明的字段
                // List<String> addFieldNames = new ArrayList<String>();
                Map<String, Object> dicAddFieldNames = new HashMap<String, Object>();
                List<Field> fields = ClassHelper.getAllFields(entityClaszz);
                List<Field> addFields = new ArrayList<>();
                for (Field field : fields) {
                    // 过滤掉不需要新增的字段
                    if (ignoreColumns != null && ignoreColumns.contains(field.getName())) {
                        continue;
                    }
                    addFields.add(field);
                }

                if (addFields.size() > 0 && entities.size() > 0) {
                    List<String> addColumns = addFields.stream().map(x -> x.getName()).collect(Collectors.toList());
                    // 方法2：使用 Java 8 Stream API
                    String strVal = addFields.stream().map(key -> "?").collect(Collectors.joining(","));
                    // 预编译sql字符串
                    String insertSql = String.format(" insert into %s(%s) values(%s); ",
                            tempTableName,
                            String.join(",", addColumns),
                            strVal);
                    ps = conn.prepareStatement(insertSql);

                    int entityCount = entities.size();

                    TEntity entity = null;
                    int j = 0;
                    for (int i = 0; i < entityCount; i++) {
                        entity = entities.get(i);
                        if (entity != null) {
                            j = 0;
                            for (Field field : addFields) {
                                // 设置可访问性
                                field.setAccessible(true);
                                ps.setObject(++j, field.get(entity));
                            }
                        }
                        // 1.攒sql
                        ps.addBatch();

                        // 2.判断设置的步长大小分批新增
                        if ((i + 1) % step == 0 || entityCount == (i + 1)) {
                            // 3.执行batch
                            int[] executeBatchResult = ps.executeBatch();
                            if (executeBatchResult != null) {
                                affectedRow += Arrays.stream(executeBatchResult).sum();
                            }
                            // 4.清除batch
                            ps.clearBatch();
                        }
                    }
                }
            } catch (Exception ex) {
                // 包装后抛出;
                throw new CompletionException(ex);
            } finally {
                disposeDbResource(ps);
            }
            return affectedRow;
        });
    }

}
